﻿using System;
using System.Data.OracleClient;
using System.Data;

namespace DAL
{
    class Oracle :IDb
    {
        OracleConnection conn = new OracleConnection();

        public Oracle(string serverName, string userID, string pwd)
        {
            conn.ConnectionString = "Data Source=" + serverName + ";" +
                                    "User Id=" + userID + ";" +
                                    "Password=" + pwd + ";" +
                                    "Integrated Security=yes";
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public System.Data.DataTable ExecuteSelectCommand(IDbCommand command)
        {
            DataTable result = new DataTable();

            try
            {
                OracleCommand oracleCommand = (OracleCommand)command;
                oracleCommand.Connection = conn;
                oracleCommand.Connection.Open();
                oracleCommand.CommandType = CommandType.Text;
                OracleDataReader oracleReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(oracleReader);
                oracleReader.Close();
                return result;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="selectStatement"></param>
        /// <returns></returns>
        public System.Data.DataTable ExecuteSelectCommand(string selectStatement)
        {
            DataTable result = new DataTable();
            try
            {
                conn.Open();
                OracleCommand oracleCommand = new OracleCommand(selectStatement);
                oracleCommand.CommandType = CommandType.Text;
                OracleDataReader oracleReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(oracleReader);
                oracleReader.Close();
                return result;

            }
            catch (Exception)
            {

                throw;
            }
        }

        public System.Data.DataTable ExecuteSelectCommand(string selectStatement, string[,] parameters)
        {
            DataTable result = new DataTable();
            try
            {
                OracleCommand oracleCommand = new OracleCommand(selectStatement);

                for (int i = 0; i < parameters.Length; i++)
                {
                    oracleCommand.Parameters.AddWithValue(parameters[i, 0], parameters[0, i]); //parameters[name, value]
                }

                conn.Open();
                OracleDataReader sqlReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(sqlReader);
                sqlReader.Close();
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}
